﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace CarInfor.DAL
{
    /// <summary>
    /// 年检
    /// </summary>
    public class YearCheck
    {
        #region 属性定义
        /// <summary>
        /// ID
        /// </summary>
        private string id = "";
        public string ID
        {
            get { return id; }
            set { id = value; }
        }
        /// <summary>
        /// 车辆号牌
        /// </summary>
        private string carNumber = string.Empty;
        public string CarNumber
        {
            get { return carNumber; }
            set { carNumber = value; }
        }
        /// 车辆类型
        /// </summary>
        private string carType = string.Empty;
        public string CarType
        {
            get { return carType; }
            set { carType = value; }
        }
        /// <summary>
        /// 业主姓名
        /// </summary>
        private string ownerName = string.Empty;
        public string OwnerName
        {
            get { return ownerName; }
            set { ownerName = value; }
        }
        /// <summary>
        /// 联系电话
        /// </summary>
        private string telephone = string.Empty;
        public string Telephone
        {
            get { return telephone; }
            set { telephone = value; }
        }
        /// <summary>
        /// 年检日期
        /// </summary>
        private string checkDate;
        public string CheckDate
        {
            get { return checkDate; }
            set { checkDate = value; }
        }
        /// <summary>
        /// 到期日期
        /// </summary>
        private string expireDate;
        public string ExpireDate
        {
            get { return expireDate; }
            set { expireDate = value; }
        }

        /// <summary>
        /// 报户时间
        /// </summary>
        private string baoHuDate = "";
        public string BaoHuDate
        {
            get { return baoHuDate; }
            set { baoHuDate = value; }
        }


        /// <summary>
        /// 备注
        /// </summary>
        private string memo = string.Empty;
        public string Memo
        {
            get { return memo; }
            set { memo = value; }
        }
        #endregion

        #region 方法
        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataTable Query()
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(""));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataSet QueryForReport()
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(""));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds;
        }

        /// <summary>
        /// 无条件查询
        /// </summary>
        public DataSet QueryForReport(string condition)
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(condition));
            }
            catch (Exception e)
            {
                throw (e);
            }
            return ds;
        }
        /// <summary>
        /// 有条件查询
        /// </summary>
        public DataTable Query(string condition)
        {
            DataSet ds = null;
            try
            {
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(condition));
            }
            catch (Exception e)
            {
                throw e;
            }
            return ds.Tables[0];
        }

        /// <summary>
        /// 根据主键查询
        /// </summary>
        public void QueryWithKey(string keyValue)
        {
            DataSet ds = null;
            try
            {
                string cond = "ID='" + keyValue + "'";
                DB db = new DB();
                ds = db.GetSqlDataSet(this.GetSqlString(cond));
                if (ds.Tables[0].Rows.Count > 0)
                {
                    id = ds.Tables[0].Rows[0]["编号"].ToString();
                    carNumber = ds.Tables[0].Rows[0]["车辆号牌"].ToString();
                    carType = ds.Tables[0].Rows[0]["车辆类型"].ToString();
                    ownerName = ds.Tables[0].Rows[0]["车主姓名"].ToString();
                    telephone = ds.Tables[0].Rows[0]["联系电话"].ToString();
                    checkDate = ds.Tables[0].Rows[0]["年检日期"].ToString();
                    expireDate = ds.Tables[0].Rows[0]["到期日期"].ToString();
                    baoHuDate = ds.Tables[0].Rows[0]["报户日期"].ToString();
                    memo = ds.Tables[0].Rows[0]["备注"].ToString();
                }
                else
                {
                    id = "";
                    carNumber = "";
                    carType = "";
                    ownerName = "";
                    telephone = "";
                    checkDate = "";
                    expireDate = "";
                    baoHuDate = "";
                    memo = "";
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
        }

        /// <summary>
        /// 获取查询Sql
        /// </summary>
        public string GetSqlString(string condition)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                sql.Append("SELECT ");
                sql.Append(" ID as 编号,CarNumber as 车辆号牌,CarType as 车辆类型,OwnerName as 车主姓名,Telephone as 联系电话,CheckDate as 年检日期,ExpireDate as 到期日期,BaoHuDate as 报户日期,Memo as 备注 ");
                sql.Append(" FROM YearCheck");
                if (!string.IsNullOrEmpty(condition))
                {
                    sql.Append(" WHERE " + condition + " order by ExpireDate  ");
                }
                else
                {
                    sql.Append(" order by ExpireDate ");
                }
            }
            catch (Exception e)
            {
                throw (e);
            }
            return sql.ToString();

        }


        /// <summary>
        /// 通过主键更新
        /// </summary>
        public int UpdateWithKey(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("UPDATE YearCheck SET ");
                sql.Append(" [CarNumber]='" + format(carNumber) + "'");
                sql.Append(",[CarType]='" + format(carType) + "'");
                sql.Append(",[OwnerName]='" + format(ownerName) + "'");
                sql.Append(",[Telephone]='" + format(telephone) + "'");
                sql.Append(",[CheckDate]='" + format(checkDate) + "'");
                sql.Append(",[ExpireDate]='" + format(expireDate) + "'");
                sql.Append(",[BaoHuDate]='" + format(baoHuDate) + "'");
                sql.Append(",[Memo]='" + format(memo) + "'");
                sql.Append(" WHERE ID='" + keyValue + "'");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 通过主键更新
        /// </summary>
        public int UpdateExpireDate(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("UPDATE YearCheck SET ");
                sql.Append(" [CarNumber]='" + format(carNumber) + "'");
                sql.Append(",[CarType]='" + format(carType) + "'");
                sql.Append(",[OwnerName]='" + format(ownerName) + "'");
                sql.Append(",[Telephone]='" + format(telephone) + "'");
                sql.Append(",[CheckDate]='" + format(checkDate) + "'");
                sql.Append(",[ExpireDate]='" + format(expireDate) + "'");
                sql.Append(",[Memo]='" + format(memo) + "'");
                sql.Append(" WHERE ID='" + keyValue + "'");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }

        /// <summary>
        ///添加一条记录
        /// </summary>
        public int Insert()
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append("INSERT into YearCheck");
                sql.Append("([ID],[CarNumber],[CarType],[OwnerName],[Telephone],[CheckDate],[ExpireDate],[BaoHuDate],[Memo]) ");
                sql.Append(" VALUES(");
                sql.Append("'" + format(id) + "'");
                sql.Append(",'" + format(carNumber) + "'");
                sql.Append(",'" + format(carType) + "'");
                sql.Append(",'" + format(ownerName) + "'");
                sql.Append(",'" + format(telephone) + "'");
                sql.Append(",'" + format(checkDate) + "'");
                sql.Append(",'" + format(expireDate) + "'");
                sql.Append(",'" + format(baoHuDate) + "'");
                sql.Append(",'" + format(memo) + "'");
                sql.Append(")");
            }
            catch (Exception e)
            {
                throw (e);
            }
            return db.ExecuteSql(sql.ToString());
        }

        /// <summary>
        /// 要求在调用IsDelete（）后使用
        /// 返回为true时，表示删除成功
        /// 返回为false时，表示删除失败
        /// </summary>
        public bool Delete(string keyValue)
        {
            StringBuilder sql = new StringBuilder();
            DB db = new DB();
            try
            {
                sql.Append(" DELETE FROM YearCheck ");
                sql.Append(" WHERE ID='" + keyValue + "'");
                db.ExecuteSql(sql.ToString());
            }
            catch (Exception e)
            {
                throw (e);
            }
            return true;
        }

        /// <summary>
        /// 单引号的处理
        /// </summary>
        public string format(string myString)
        {
            return myString.Replace("'", "''");
        }

        #endregion
    }
}